package com.business.util;


import org.apache.commons.dbutils.*;
import org.apache.commons.dbutils.handlers.*;

import javax.sql.DataSource;
import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * Created by meng on 15-11-16.
 */

public class TouchJdbc {


	private static org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(TouchJdbc.class);
	
	private DataSource dataSource;


//	在JDK 5之后，Java使用了新的内存模型。volatile关键字有了明确的语义——在JDK1.5之前，volatile是个关键字，
//  但是并没有明确的规定其用途——被volatile修饰的写变量不能和之前的读写代码调整，读变量不能和之后的读写代码调整！
	private volatile static TouchJdbc touchJdbc = null;

	private TouchJdbc(){}

	//单例实现的一种方式
	public static TouchJdbc getInstance(){
		if(touchJdbc==null){
			synchronized(TouchJdbc.class){
				if(touchJdbc==null){
					touchJdbc = new TouchJdbc();
					logger.info("获取 TouchJdbc 成功！");
				}
			}
		}
		return touchJdbc;
	}


	public DataSource getDataSource() {
		return dataSource;
	}



	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}




	/**
	 * 查询某表中某列的值，如果该列的值存在多个，返回最后一个
	 * eg: select create_time from person where age = '11'
	 *
	 * @param tableName			表名
	 * @param primaryColumnName 主键列名
	 * @param primaryColumnVal	主键列值
	 * @param oneColumnName		查询列名
	 * @return
	 */
	public String peekOneColumn(String tableName, String primaryColumnName,
								String primaryColumnVal, String oneColumnName) {
		StringBuffer sb = new StringBuffer();
		sb.append("select ").append(oneColumnName).append(" from ")
				.append(tableName).append(" where ").append(primaryColumnName)
				.append(" = ").append("'" + primaryColumnVal + "'");
		logger.info("sql --> "+sb.toString());
		return peekOneColumn(sb.toString());
	}


	public String peekOneColumn(Connection conn, String tableName, String primaryColumnName,
								String primaryColumnVal, String oneColumnName) {
		StringBuffer sb = new StringBuffer();
		sb.append("select ").append(oneColumnName).append(" from ")
				.append(tableName).append(" where ").append(primaryColumnName)
				.append(" = ").append("'" + primaryColumnVal + "'");
		logger.info("sql --> "+sb.toString());
		return peekOneColumn(conn,sb.toString());
	}


	/**
	 * 按照给定的sql，查询某表中某列的值，如果该列的值存在多个，返回最后一个
	 * eg: select create_time from person where age = '11' and name = '小明'
	 *
	 * @param sql
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String peekOneColumn(String sql, Object... param) {

		QueryRunner runner = new QueryRunner(dataSource);

		List<Object> columnResult = null;
		try {
			columnResult = (List<Object>)runner.query(sql, new ColumnListHandler(),param);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if(isNotNull(columnResult)){
			return String.valueOf(columnResult.get(columnResult.size()-1));
		}else{
			return null;
		}
	}


	public String peekOneColumn(Connection conn, String sql, Object... param) {

		QueryRunner runner = new QueryRunner();

		List<Object> columnResult = null;
		try {
			columnResult = (List<Object>)runner.query(conn,sql, new ColumnListHandler(),param);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if(isNotNull(columnResult)){
			return String.valueOf(columnResult.get(columnResult.size()-1));
		}else{
			return null;
		}
	}


	/**
	 * 查询某表中某列的值，以List集合方式返回
	 * eg: select create_time from person where age = '11'
	 *
	 * @param tableName 		表名
	 * @param primaryColumnName 主键列
	 * @param primaryColumnVal  主键值
	 * @param oneColumnName     查询列
	 * @return
	 */
	public List<Object> peekColumnVals(String tableName, String primaryColumnName,
									   String primaryColumnVal, String oneColumnName) {
		StringBuffer sb = new StringBuffer();
		sb.append("select ").append(oneColumnName).append(" from ")
				.append(tableName).append(" where ").append(primaryColumnName)
				.append(" = ").append("'" + primaryColumnVal + "'");
		return peekColumnVals(sb.toString());
	}


	public List<Object> peekColumnVals(Connection conn, String tableName, String primaryColumnName,
									   String primaryColumnVal, String oneColumnName) {
		StringBuffer sb = new StringBuffer();
		sb.append("select ").append(oneColumnName).append(" from ")
				.append(tableName).append(" where ").append(primaryColumnName)
				.append(" = ").append("'" + primaryColumnVal + "'");
		return peekColumnVals(conn,sb.toString());
	}


	/**
	 * 按照给定的sql，查询某表中某列的值，以List集合方式返回
	 * eg: select create_time from person where age = '11' and name = '小明'
	 *
	 * @param sql
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Object> peekColumnVals(String sql, Object... param) {
		QueryRunner runner = new QueryRunner(dataSource);

		List<Object> columnResult = null;
		try {
			columnResult = (List<Object>)runner.query(sql, new ColumnListHandler(),param);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return columnResult;
	}


	public List<Object> peekColumnVals(Connection conn, String sql, Object... param) {
		QueryRunner runner = new QueryRunner();

		List<Object> columnResult = null;
		try {
			columnResult = (List<Object>)runner.query(conn,sql, new ColumnListHandler(),param);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return columnResult;
	}


	/**
	 * 统计某表中某列值的个数
	 * eg: select count(*) from person where age = 12
	 *
	 * @param tableName		表名
	 * @param columnName	列名
	 * @param columnVal		列值
	 * @return				返回统计个数
	 */
	public int countOneColumn(String tableName, String columnName, String columnVal){
		StringBuffer sb = new StringBuffer();
		sb.append("select count(*)").append(" from ")
				.append(tableName).append(" where ").append(columnName)
				.append(" = ").append("'" + columnVal + "'");
		return countData(sb.toString());
	}


	public int countOneColumn(Connection conn, String tableName, String columnName, String columnVal){
		StringBuffer sb = new StringBuffer();
		sb.append("select count(*)").append(" from ")
				.append(tableName).append(" where ").append(columnName)
				.append(" = ").append("'" + columnVal + "'");
		return countData(conn,sb.toString());
	}

	/**
	 * 按照给定的sql，统计数据库中的个数
	 * eg: select count(*) from person where age = 12 and name = '小明'
	 * @param sql	给定的sql
	 * @return 		返回统计个数
	 */
	public int countData(String sql, Object... param){
		QueryRunner runner = new QueryRunner(dataSource);

		try {
			Object[] arr = runner.query(sql,new ArrayHandler(),param);
			return Integer.parseInt(String.valueOf(arr[0]));
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
	}


	public int countData(Connection conn, String sql, Object... param){
		QueryRunner runner = new QueryRunner();
		try {
			Object[] arr = runner.query(conn,sql,new ArrayHandler(),param);
			return Integer.parseInt(String.valueOf(arr[0]));
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
	}


	/**
	 * 删除表中记录
	 * eg: delete from person where age = 123
	 *
	 * @param tableName		表名
	 * @param columnName	列名
	 * @param columnVal		列值
	 * @return				删除个数
	 */
	public Integer delRecords(String tableName, String columnName, String columnVal){
		StringBuffer sb = new StringBuffer();
		sb.append("delete from ").append(tableName).append(" where ").append(columnName)
				.append(" = ").append("'" + columnVal + "'");
		logger.info("sql --> "+sb.toString());
		return delRecords(sb.toString());
	}

	/**
	 * 按照给定的sql，删除表中记录
	 * eg: delete from person where age = 1 and info_id = 25
	 *
	 * @param sql	给定的sql
	 * @return		删除个数
	 */
	public Integer delRecords(String sql, Object... param){
		QueryRunner runner = new QueryRunner(dataSource);

		int count = 0;
		try {
			count = runner.update(sql,param);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}



	/**
	 * 包含事务的删除，Connection 应设置事务不自动提交
	 * @param conn
	 * @param sql
	 * @param param
	 * @return
	 * @throws SQLException
	 */
	public Integer delRecords(Connection conn, String sql, Object... param) throws SQLException {
		QueryRunner runner = new QueryRunner();
		int count = 0;
		count = runner.update(conn,sql,param);
		return count;
	}

	/**
	 * 更新某表中某列的值
	 * eg: update person set [desc] = 'ww123' where info_id = 31
	 *
	 * @param tableName				表名
	 * @param primaryColumnName		主键列名
	 * @param primaryColumnVal		主键列值
	 * @param columnName			更新列名
	 * @param columnVal				更新列值
	 * @return						更新个数
	 */
	public Integer updateColumn(String tableName, String primaryColumnName,
								String primaryColumnVal, String columnName, String columnVal){
		StringBuffer sb = new StringBuffer();
		sb.append("update ").append(tableName).append(" set [").append(columnName)
				.append("] = ").append("'" + columnVal + "'").append(" where ")
				.append(primaryColumnName).append(" = ").append("'"+primaryColumnVal+"'");
		logger.info("sql --> "+sb.toString());
		return updateColumn(sb.toString());
	}



	/**
	 * 按照给定的sql，更新某表中某列的值
	 * eg: update person set [desc] = 'ww123' where name = '小明' and info_id = 31
	 *
	 * @param sql	给定的sql
	 * @return		更新个数
	 */
	public Integer updateColumn(String sql, Object... param){
		QueryRunner runner = new QueryRunner(dataSource);
		int count = 0;
		try {
//	这种sql执行时DBUtils会出错		sql = "update survey set title = ?,validity = ? where uuid = 'a'";
//	这种sql执行时DBUtils不会出错	sql = "update survey set title = ? , validity = ? where uuid = 'a'";  参数(?)和逗号(,)之间要有空格，否则出错！
			count = runner.update(sql,param);

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	public Integer update(String sql, Object... param){
		return updateColumn(sql,param);
	}

	public Integer update(Connection conn, String sql, Object... param) throws SQLException {
		return updateColumn(conn,sql,param);
	}


	/**
	 * 包含事务的更新，Connection 应设置事务不自动提交
	 * @param conn
	 * @param sql
	 * @param param
	 * @return
	 * @throws SQLException
	 */
	public Integer updateColumn(Connection conn, String sql, Object... param) throws SQLException {
		QueryRunner runner = new QueryRunner();
		int count = 0;
		count = runner.update(conn,sql,param);
		return count;
	}



	/**
	 * 将 Map 中的值插入到 tableName 中
	 * @param tableName
	 * @param map 参数说明如下：
	 * 				 如 person 表中有 name,age 等若干列,
	 * 			     Map<String,Object> param = new HashMap<String,Object>();
	 * 			     map.put("name","小张");
	 * 			     map.put("age","25");
	 * 			     ……		需要插入的列都put到Map中即可
	 *
	 * 			     调用：List<String> result = insertRecord（"person",map）;
	 *
	 * @return  返回:1.插入影响的行数(list.get(0))		2:执行的sql和参数(list.get(1))
	 */
	public List<String> insertRecord(String tableName, Map<String,Object> map){
		String methodName = Thread.currentThread().getStackTrace()[1].getMethodName();

		QueryRunner runner = new QueryRunner(dataSource);

		int flag = 0;
		List<String> result = new ArrayList<String>();

		if(map!=null){
			List<String> param = new ArrayList<String>();
			String columns = "";
			String vals = "";
			String params = " [ ";
			for(String key:map.keySet()){

				columns += "[";
				columns += key;
				columns += "]";
				columns += ",";

				vals += "?";
				vals += ",";
				params += String.valueOf(map.get(key))+" ";
				param.add(String.valueOf(map.get(key)));
			}
			params += "] ";
			String sql = "";
			if( isNotNull(columns) && isNotNull(vals) ){
				columns = columns.substring(0,columns.length()-1);
				vals = vals.substring(0,vals.length()-1);

				sql = "insert into "+tableName+" ("+columns+") values("+vals+")";
				try {
					flag = runner.update(sql, param.toArray());
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			result.add(0,flag+"");
			result.add(1,sql+" 参数为："+params);
		}
		return result;
	}


	/**
	 * 包含事务的插入，Connection 应设置事务不自动提交
	 * @param conn
	 * @param tableName
	 * @param map
	 * @return
	 */
	public List<String> insertRecord(Connection conn, String tableName, Map<String,Object> map) throws SQLException {
		String methodName = Thread.currentThread().getStackTrace()[1].getMethodName();

		QueryRunner runner = new QueryRunner();

		int flag = 0;
		List<String> result = new ArrayList<String>();

		if(map!=null){
			List<String> param = new ArrayList<String>();
			String columns = "";
			String vals = "";
			String params = " [ ";
			for(String key:map.keySet()){

//				columns += "[";
				columns += key;
//				columns += "]";
				columns += ",";

				vals += "?";
				vals += ",";
				params += String.valueOf(map.get(key))+" ";
				param.add(String.valueOf(map.get(key)));
			}
			params += "] ";
			String sql = "";
			if( isNotNull(columns) && isNotNull(vals) ){
				columns = columns.substring(0,columns.length()-1);
				vals = vals.substring(0,vals.length()-1);

				sql = "insert into "+tableName+" ("+columns+") values("+vals+")";
				flag = runner.update(conn,sql, param.toArray());

			}
			result.add(0,flag+"");
			result.add(1,sql+" 参数为："+params);
		}
		return result;
	}



	/**
	 * 包含事务的批处理，Connection 应设置事务不自动提交
	 * @param conn
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public int[] batch(Connection conn, String sql, Object[][] params) throws SQLException {

		QueryRunner runner = new QueryRunner();

//		Object[][] params = new Object[][] { { "user1", "pwd1" }, { "user2", "pwd2" } };
//		int[] result = runner.batch("insert into user (username, password) values (?, ?)",params);

		int[] result = runner.batch(conn,sql,params);

//		logger.info("批处理操作，返回相应结果数组" + Arrays.toString(result));

		return result;
	}




	/**
	 * 根据给定的sql，将查询结果转换为Map，如果查询出 n 条记录，只将第 1 条记录转为 Map返回
	 * @param sql
	 * @param param  替换sql中的 ？占位符
	 * @return
	 */
	public Map<String, Object> query4Map(String sql, Object... param){
		QueryRunner runner = new QueryRunner(dataSource);
		Map<String, Object> mapResult = new HashMap<String, Object>();
		try {
			mapResult = runner.query(sql, new MapHandler(),param);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return mapResult;
	}


	public Map<String, Object> query4Map(Connection conn, String sql, Object... param){
		QueryRunner runner = new QueryRunner();
		Map<String, Object> mapResult = new HashMap<String, Object>();
		try {
			mapResult = runner.query(conn,sql, new MapHandler(),param);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return mapResult;
	}

	public int query4Int(Connection conn, String sql, Object... param){
		int result = 0;
		Map<String, Object> map = query4Map(conn,sql,param);
		for (String key:map.keySet()){
			Object obj = map.get(key);
			result = Integer.parseInt(obj.toString());
			break;
		}
		return result;
	}

	public long query4Long(Connection conn, String sql, Object... param){
		long result = 0L;
		Map<String, Object> map = query4Map(conn,sql,param);
		for (String key:map.keySet()){
			Object obj = map.get(key);
			result = Long.parseLong(obj.toString());
			break;
		}
		return result;
	}


	/**
	 * 根据给定的sql，将查询结果转换为Map集合
	 * @param sql
	 * @param param  替换sql中的 ？占位符
	 * @return
	 */
	public List<Map<String,Object>> query4MapList(String sql, Object... param){
		QueryRunner runner = new QueryRunner(dataSource);
		List<Map<String,Object>> mapListResult = new ArrayList<Map<String,Object>>();
		try {
			mapListResult = runner.query(sql, new MapListHandler(),param);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return mapListResult;
	}



	public List<Map<String,Object>> query4MapList(Connection conn, String sql, Object... param){
		QueryRunner runner = new QueryRunner();
		List<Map<String,Object>> mapListResult = new ArrayList<Map<String,Object>>();
		try {
			mapListResult = runner.query(conn,sql, new MapListHandler(),param);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return mapListResult;
	}


	/**
	 * 根据给定的sql和Class，将查询结果转成Bean，如果结果有 n 条，只返回第 1 条
	 * 要求：1.表中字段(命名样式)：id,name,create_time
	 * 	   2.类中属性(命名样式)：id,name,createTime (即骆蜂命名)
	 * @param clazz
	 * @param sql
	 * @param param 替换sql中的 ？占位符
	 * @return
	 */
	public <T> T query4Bean(Class<T> clazz, String sql, Object... param){
		QueryRunner runner = new QueryRunner(dataSource);

		BeanProcessor beanProcessor = new GenerousBeanProcessor();
		RowProcessor rowProcessor = new BasicRowProcessor(beanProcessor);
		BeanHandler<T> h = new BeanHandler<T>(clazz,rowProcessor);

		T t = null;
		try {
			t = runner.query(sql, h,param);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return t;
	}


	public <T> T query4Bean(Connection conn, Class<T> clazz, String sql, Object... param){
		QueryRunner runner = new QueryRunner();

		BeanProcessor beanProcessor = new GenerousBeanProcessor();
		RowProcessor rowProcessor = new BasicRowProcessor(beanProcessor);
		BeanHandler<T> h = new BeanHandler<T>(clazz,rowProcessor);

		T t = null;
		try {
			t = runner.query(conn,sql, h,param);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return t;
	}



	/**
	 * 根据给定的sql和Class，将查询结果转成Bean的集合
	 * 要求：1.表中字段(命名样式)：id,name,create_time
	 * 	   2.类中属性(命名样式)：id,name,createTime,uuid (即骆蜂命名)
	 * @param clazz
	 * @param sql
	 * @param param 替换sql中的 ？占位符
	 * @return
	 */
	public <T> List<T> query4BeanList(Class<T> clazz, String sql, Object... param){
		QueryRunner runner = new QueryRunner(dataSource);

		BeanListHandler<T> h = new BeanListHandler<T>(clazz,new BasicRowProcessor(new GenerousBeanProcessor()));

		List<T> list = null;
		try {
			list = runner.query(sql, h,param);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}



	public <T> List<T> query4BeanList(Connection conn, Class<T> clazz, String sql, Object... param){
		QueryRunner runner = new QueryRunner();

		BeanListHandler<T> h = new BeanListHandler<T>(clazz,new BasicRowProcessor(new GenerousBeanProcessor()));

		List<T> list = null;
		try {
			list = runner.query(conn,sql, h,param);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}



	/**
	 * 根据给定的sql和Class，将查询结果转成Bean，如果结果有 n 条，只返回第 1 条
	 * 此种情况完全改变表字段到Javabean属性的映射，map.put(表字段名称, Javabean属性名称)
	 *
	 * @param clazz
	 * @param sql
	 * @param map
	 * @param param 替换sql中的 ？占位符
	 * @return
	 */
	public <T> T query4Bean(Class<T> clazz, String sql, Map<String, String> map, Object... param){
		QueryRunner runner = new QueryRunner(dataSource);

		BeanProcessor beanProcessor = new BeanProcessor(map);
		RowProcessor rowProcessor = new BasicRowProcessor(beanProcessor);
		BeanHandler<T> h = new BeanHandler<T>(clazz,rowProcessor);

		T t = null;
		try {
			t = runner.query(sql, h,param);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return t;
	}


	/**
	 * 根据给定的sql和Class，将查询结果转成List
	 * 此种情况完全改变表字段到Javabean属性的映射，map.put(表字段名称, Javabean属性名称)
	 * @param clazz
	 * @param sql
	 * @param map
	 * @param param  替换sql中的 ？占位符
	 * @return
	 */
	public <T> List<T> query4BeanList(Class<T> clazz, String sql, Map<String, String> map, Object... param){
		QueryRunner runner = new QueryRunner(dataSource);

		BeanListHandler<T> h = new BeanListHandler<T>(clazz,new BasicRowProcessor(new BeanProcessor(map)));

		List<T> list = null;
		try {
			list = runner.query(sql,h,param);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}


	// 如果不是空返回 true
	public static boolean isNotNull(List list) {
		if (list != null && list.size() > 0)
			return true;
		else
			return false;
	}

	// 如果不是空返回 true
	public static boolean isNotNull(Object[] arr) {
		if (arr != null && arr.length > 0)
			return true;
		else
			return false;
	}


	// 如果是空返回 true
	public static boolean isNull(List list) {
		return !isNotNull(list);
	}

	// 如果是空返回 true
	public static boolean isNull(Object[] arr) {
		return !isNotNull(arr);
	}

	// 如果不是空返回 true
	public static boolean isNotNull(String str) {
		if ((str != null) && (!"".equals(str)))
			return true;
		else
			return false;
	}

	// 如果是空返回 true
	public static boolean isNull(String str) {
		return !isNotNull(str);
	}


	/**
	 *
	 * 开启事务后，查询和增删改不能一起用，JDBC会报不是同一个线程错误！
	 * @return
	 */
	public Connection getConnection(){
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			conn.setAutoCommit(false);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}


	//返回开启事务的 Connection
	public Connection getTransactionConn(Connection conn){
		try {
			conn.setAutoCommit(false);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}


	/**
	 *
	 * @param conn
	 * @param close  是否关闭 Connection
	 */
	public static void rollback(Connection conn, boolean close){
		if (close){
			DbUtils.rollbackAndCloseQuietly(conn);//会过滤掉任何风险，回滚事物并关闭连接
		}else{
			try {
				conn.rollback();
				conn.setAutoCommit(true);//回滚后，关闭 Connection 的事务
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 *
	 * @param conn
	 * @param close		是否关闭 Connection
	 */
	public static void commit(Connection conn, boolean close){
		if(close){
			DbUtils.commitAndCloseQuietly(conn);//会过滤掉任何风险，提交事务并关闭连接
		}else {
			try {
				conn.commit();
				conn.setAutoCommit(true);//提交后，关闭 Connection 的事务
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}


	/**
	 通过 DbUtils 手动管理事务 事例：

	 public Integer delSurveyAnswerInfo(String uuid){
	 Connection conn = touchJdbc.getConnection();//含有事务的连接
	 int count = 0;
	 try{
	 //这两个删除处于一个事务中
	 touchJdbc.delRecords(conn,"delete from survey_ques_answer where info_uuid = '"+uuid+"'");//删除答案
	 count = touchJdbc.delRecords(conn,"delete from survey_answer_info where uuid = '"+uuid+"'");//删除信息
	 }catch (Exception e){
	 count = 0;
	 DbUtils.rollbackAndCloseQuietly(conn);//回滚事务并关闭连接
	 e.printStackTrace();
	 }
	 DbUtils.commitAndCloseQuietly(conn);//提交事务并关闭连接
	 return count;
	 }

	 */


	/**
	 * 查询当前数据库中的所有表名，存储函数也会查出来
	 * @return
	 */
	public List<String> getTables(){
		String sql = "";

		sql += "SELECT                                     ";
		sql += "b.name AS tablename ";
//		sql += "		a.rowcnt AS datacount ";
		sql += "FROM ";
		sql += "sysindexes a,";
		sql += "sysobjects b ";
		sql += "WHERE ";
		sql += "a.id = b.id ";
		sql += "AND a.indid < 2 ";
		sql += "AND objectproperty(b.id, 'IsMSShipped') = 0";
		List<Object> list = peekColumnVals(sql);

		return HelpMe.objList2StrList(list);
	}

	/**
	 * 从 Connection 中获得数据库中所有的表名
	 * @return
	 */
	public List<String> getTables2(){
		List<String> list = new ArrayList<String>();
		try{
			Connection conn = dataSource.getConnection();
			DatabaseMetaData metaData = conn.getMetaData();
			ResultSet rs = metaData.getTables(conn.getCatalog(), null, null, new String[]{"TABLE"});
			while(rs.next()) {
				list.add(rs.getString("TABLE_NAME"));
			}
		}catch (Exception e){
			e.printStackTrace();
		}
		return list;
	}


	/**
	 * 通过表名，查询表中的所有字段
	 * @param tableName
	 * @return
	 */
	public List<String> getTableColumn(String tableName){
		String sql = "select name from syscolumns Where ID=OBJECT_ID('"+tableName+"')";
		return HelpMe.objList2StrList(peekColumnVals(sql));
	}




	/**
	 * 示例：
	 * @param tableName  --> person
	 * @param timeColumn	--> create_time
	 * @param recentlyDay	--> 7(10,30等)
	 * @param currDate	--> new Date()
	 * @return --> 统计 person 表中，最近 7 (10,30)天每天的数据总和，其中 timeColumn 是表中的时间列（按哪个列统计），如 create_time
	 * 统计结果以 List<Map<String,Object>> 返回。
	 */
	public List<Map<String,Object>> recentlyCountByDay(String tableName, String timeColumn, Integer recentlyDay, Date currDate){
		List<String> recentlyDayList = HelpMe.recentlyDayList(currDate,recentlyDay);
		List<String> castList = HelpMe.castRecentlyDayList(recentlyDayList);
		
		String sql = "exec recentlyCountByDay ?,?,?,?,?";//存储过程
		Object[] param = new Object[]{tableName,timeColumn,castList.get(0),castList.get(1),castList.get(2)};
		List<Map<String, Object>> mapList = query4MapList(sql,param);
		
		return mapList;
	}
	
	/**
	 * 示例：
	   @param tableName  --> person
	 * @param timeColumn	--> create_time
	 * @param currDate	--> new Date()
	 * @param firstDayOfWeek  指示一个星期中的第一天  Calendar.SUNDAY（以周日为首日）  Calendar.MONDAY（以周一为首日）
	 * @param flag	--> true(统计本周) false(统计上周)
	 * @return --> 统计 person 表中，本（上）周 7 天每天的数据总和，其中 timeColumn 是表中的时间列（按哪个列统计），如 create_time
	 * 统计结果以 List<Map<String,Object>> 返回。
	 */
	public List<Map<String,Object>> weekCount(String tableName, String timeColumn, Date currDate, int firstDayOfWeek, boolean flag){
		
		List<String> recentlyDayList = HelpMe.weekDayList(currDate,firstDayOfWeek,flag);
		List<String> castList = HelpMe.castRecentlyDayList(recentlyDayList);
		
		String sql = "exec recentlyCountByDay ?,?,?,?,?";//存储过程
		Object[] param = new Object[]{tableName,timeColumn,castList.get(0),castList.get(1),castList.get(2)};
		List<Map<String, Object>> mapList = query4MapList(sql,param);
		
		return mapList;
	}
	
	
	/**
	 * 
	 * 示例：
	 * @param tableName  --> person
	 * @param timeColumn	--> create_time
	 * @param currDate	--> new Date()
	 * @param flag	--> true(统计本月) false(统计上月)
	 * @return --> 统计 person 表中，本（上）月每天的数据总和，其中 timeColumn 是表中的时间列（按哪个列统计），如 create_time
	 * 统计结果以 List<Map<String,Object>> 返回。
	 */
	public List<Map<String,Object>> monthCount(String tableName, String timeColumn, Date currDate, boolean flag){
		
		Date firstDay = HelpMe.getFirstDayOfMonth(currDate);
		int dayCount = 0;
		if(flag){//本月
			dayCount = HelpMe.getDayCountByMonth(currDate);
		}else {//上月
			Date prevDate = HelpMe.prevDate(firstDay, 1);
			firstDay = HelpMe.getFirstDayOfMonth(prevDate);
			dayCount = HelpMe.getDayCountByMonth(prevDate);
		}
		
		List<String> recentlyDayList = HelpMe.recentlyDayList(firstDay,dayCount,"asdf");
		List<String> castList = HelpMe.castRecentlyDayList(recentlyDayList);
		
		
		String sql = "exec recentlyCountByDay ?,?,?,?,?";//存储过程
		Object[] param = new Object[]{tableName,timeColumn,castList.get(0),castList.get(1),castList.get(2)};
		List<Map<String, Object>> mapList = query4MapList(sql,param);
		
		return mapList;
	}




	public List<String> procedureCall(String procName, String queryColumn, String... param){
		String methodName = Thread.currentThread().getStackTrace()[1].getMethodName();

//		String procedure = "{call "+procName+"(  ?,?,?)}";
		String procedure = "{call "+procName+"(";

		if(param!=null&&param.length>0){
			for(int i=0;i<param.length;i++){
				procedure += "?,";
			}
			procedure = procedure.substring(0,procedure.length()-1);
			procedure += ")}";
		}

		Connection conn = getConnection();
		List<String> result = new ArrayList<String>();
		try{

			ResultSet rs = null;

			CallableStatement proc = conn.prepareCall(procedure);
			// 设置输入参数
			for(int i=0;i<param.length;i++){
				proc.setString(i+1,param[i]);
			}

			// 调用存储过程
			proc.execute();

			// 取出存储过程的结果集
			boolean hasResult = true;

			while (hasResult) {
				rs = proc.getResultSet();
				if(rs!=null){
					while(rs.next()) {
						result.add(rs.getString(queryColumn));
					}
					hasResult = proc.getMoreResults();
				}else{
					hasResult = false;
				}
			}
			commit(conn,true);
		}catch (Exception e){
			e.printStackTrace();
			rollback(conn,true);
		}
		return result;
	}


}
